package com.company.project.util;

import com.company.project.entity.SysOrderEntity;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.thymeleaf.util.TextUtils;

import java.io.IOException;
import java.io.InputStream;
import java.io.PushbackInputStream;
import java.lang.reflect.Field;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;


@Slf4j
public class ExcelUtil {

    public static Workbook create(InputStream in) throws
            IOException, InvalidFormatException {
        if (!in.markSupported()) {
            in = new PushbackInputStream(in, 8);
        }
        if (POIFSFileSystem.hasPOIFSHeader(in)) {
            return new HSSFWorkbook(in);
        }
        if (POIXMLDocument.hasOOXMLHeader(in)) {
            return new XSSFWorkbook(OPCPackage.open(in));
        }
        throw new IllegalArgumentException("你的excel版本目前poi解析不了");

    }

    public static <T>List<T> excelToList(String fileName , InputStream in, Class<T> clazz, Map<String,String> excelHeads, String dateString) throws Exception {
        List<T> list = new ArrayList<>();
//        Workbook sheets = getWorkbook(in, fileName);
        Workbook work = create(in);
        Sheet sheets = work.getSheetAt(0);
//        XSSFWorkbook sheets = new XSSFWorkbook(in);
        Row head = null;
//        for (Workbook sheet : sheets) {
            for (Row row : sheets) {
                if (row.getRowNum()==0){
                    head = row;
                    continue;
                }
                int number = row.getPhysicalNumberOfCells();
                T t = clazz.getConstructor().newInstance();
                for (int i = 0; i <number; i++) {
                    String stringCellValue = head.getCell(i).getStringCellValue();
                    String beanFieldName = excelHeads.get(stringCellValue);//获得对应的字段名
                    System.out.println("beanFileName :" +beanFieldName);
                    if (beanFieldName == null || beanFieldName.equals("")) {
                        continue;
                    }
                    Field field = t.getClass().getDeclaredField(beanFieldName);//获取字段
                    field.setAccessible(true);
                    Cell cell = row.getCell(i);//获取单元格
                    if (cell==null){
                        number++;
                        continue;
                    }
                    Class<?> type = field.getType();//
                    System.out.println("bean type is :" +type);
                    if (type.equals(String.class)){
                        field.set(t, getCellValue(cell));
                    }else if (type.equals(Integer.class)){
                        field.set(t, Integer.valueOf(getCellValue(cell)));
                    }else if (type.equals(Long.class.getName())){
                        field.set(t,Long.valueOf(getCellValue(cell)));
                    }else if (type.equals(Float.class)){
                        field.set(t, Float.valueOf(getCellValue(cell)));
                    }else if (type.equals(Double.class)){
                        field.set(t, Double.valueOf(getCellValue(cell)));
                    }else if (type.equals(Byte.class)){
                        field.set(t, Byte.valueOf(getCellValue(cell)));
                    }else if (type.equals(Boolean.class)){
                        field.set(t, Boolean.valueOf(getCellValue(cell)));
                    }else if (type.equals(Date.class)){
                        SimpleDateFormat sdf = new SimpleDateFormat(dateString);
                        Date date=null;
                        try {
                            date=sdf.parse(getCellValue(cell));
                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                        field.set(t, date);
                    }else {
                        field.set(t, getCellValue(cell));
                    }
                }
                list.add(t);
            }
//        }
        return list;
    }


    private static String getCellValue(Cell cell) {
        Object result = "";
        if (cell != null) {
            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    result = cell.getStringCellValue();
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        Date theDate = cell.getDateCellValue();
                        SimpleDateFormat dff = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                        result = dff.format(theDate);
                    }else{
                        DecimalFormat df = new DecimalFormat("0");
                        result = df.format(cell.getNumericCellValue());
                    }
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    result = cell.getBooleanCellValue();
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    result = cell.getCellFormula();
                    break;
                case Cell.CELL_TYPE_ERROR:
                    result = cell.getErrorCellValue();
                    break;
                case Cell.CELL_TYPE_BLANK:
                    break;
                default:
                    break;
            }
        }
        return result.toString();
    }


//    /**
//     * 处理上传的文件
//     *
//     * @param in
//     * @param fileName
//     * @return
//     * @throws Exception
//     */
//    public static List readExcel(InputStream in, String fileName) throws Exception {
//
//        List list = new ArrayList<>();
//        log.info("======[readExcel]========="+fileName);
//        //创建Excel工作薄
//        Workbook work = getWorkbook(in, fileName);
//        if (null == work) {
//            throw new Exception("创建Excel工作薄为空！");
//        }
//        Row row = null;
//        Cell cell = null;
//
//        Sheet sheet = work.getSheetAt(0);
//        log.info("======[readExcel]====(sheet)====="+sheet);
//        // 获取第一个实际行的下标
//        log.info("======sheet.getFirstRowNum()========="+sheet.getFirstRowNum());
//        // 获取最后一个实际行的下标
//        log.info("======sheet.getLastRowNum()========="+sheet.getLastRowNum());
//        for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
//
//            row = sheet.getRow(i);
////            // 获取在某行第一个单元格的下标
////            log.info("======row.getFirstCellNum()========="+row.getFirstCellNum());
////            // 获取在某行的列数
////            log.info("======row.getLastCellNum()========="+row.getLastCellNum());
//            if (row == null || row.getFirstCellNum() == i) {
//
//                continue;
//            }
//            List<Object> li = new ArrayList<>();
//            for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
//
//                cell = row.getCell(j);
////                log.info("======"+ cell.get);
//                SysOrderEntity entity = new SysOrderEntity();
////                entity.setPurchase_id();
//                li.add(cell);
//            }
//            list.add(li);
//        }
//        work.close();//这行报错的话  看下导入jar包的版本
//        return list;
//    }
//
//    /**
//     * 判断文件格式
//     *
//     * @param inStr
//     * @param fileName
//     * @return
//     * @throws Exception
//     */
    public static Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {

        Workbook workbook = null;
        String fileType = fileName.substring(fileName.lastIndexOf("."));
        log.info("==========fileType========"+fileType);

        if (".xls".equals(fileType)) {
            workbook = new HSSFWorkbook(inStr);
        } else if (".xlsx".equals(fileType)) {
            workbook = new XSSFWorkbook(inStr);
        } else {
            throw new Exception("请上传excel文件！");
        }

        log.info("==========workbook========"+workbook);

        return workbook;
    }
}
